SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


IF OBJECT_ID('dbo.[Member]', N'U') IS NOT NULL
DROP TABLE [dbo].[Member]
GO

/****** Object:  Table [dbo].[Member]    Script Date: 4/29/2014 2:17:45 PM ******/
CREATE TABLE [dbo].[Member](
	[Id] [uniqueidentifier] NOT NULL,
	[Name] [nvarchar](256) NOT NULL,
	[TagId] [varchar](64) NULL,
	[Code] [varchar](64) NULL,
	[DateOfBirth] [datetime] NULL,
	[Address] [nvarchar](256) NULL,
	[Email] [varchar](128) NOT NULL,
	[Mobile] [varchar](32) NULL,
	[ScreenMessage] [varchar](512) NULL,
	[ValidFrom] [datetime] NULL,
	[ValidTo] [datetime] NULL,
	[SendSms] [bit] NULL,
	[SendEmail] [bit] NULL,
	[UsePrint] [bit] NULL,
	[Block] [bit] NULL,
	[PasswordHash] [varchar](256) NULL,
	[PasswordSalt] [varchar](256) NULL,
	[FailedAttemptCount] [integer] NULL,
	[RoleValue] [char](2) NULL,
	[UserStatusValue] [char](2) NULL,
	[PasswordRetrievalToken] [varchar](256) NULL,
	[LastLoginTime] [datetime] NULL,
	[PasswordRetrievalTokenExpirationDate] [datetime] NULL,
	[LastPasswordChangedDate] [datetime] NULL,
	[Session] [varchar](256) NULL,
	[Description] [nvarchar](2048) NULL,
	[ImageData] [varchar](max) NULL,
	[RevisionNumber] [int] NULL,
	[CreatedBy] [uniqueidentifier] NULL,
	[CreatedOn] [datetime] NULL,
	[ModifiedBy] [uniqueidentifier] NULL,
	[ModifiedOn] [datetime] NULL,
CONSTRAINT [PK_Member] PRIMARY KEY CLUSTERED 
	([Id] ASC)
	WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
	) ON [PRIMARY]
GO


IF OBJECT_ID('dbo.[Member_Log]', N'U') IS NOT NULL
DROP TABLE [dbo].[Member_Log]
GO

/****** Object:  Table [dbo].[Member_Log]    Script Date: 4/29/2014 2:17:45 PM ******/

CREATE TABLE [dbo].[Member_Log](
	[Sr] [bigint] IDENTITY(1,1) NOT NULL,
	[Id] [uniqueidentifier] NOT NULL,
	[Name] [nvarchar](256) NOT NULL,
	[TagId] [varchar](64) NULL,
	[Code] [varchar](64) NULL,
	[DateOfBirth] [datetime] NULL,
	[Address] [nvarchar](256) NULL,
	[Email] [varchar](128) NOT NULL,
	[Mobile] [varchar](32) NULL,
	[ScreenMessage] [varchar](512) NULL,
	[ValidFrom] [datetime] NULL,
	[ValidTo] [datetime] NULL,
	[SendSms] [bit] NULL,
	[SendEmail] [bit] NULL,
	[UsePrint] [bit] NULL,
	[Block] [bit] NULL,
	[PasswordHash] [varchar](256) NULL,
	[PasswordSalt] [varchar](256) NULL,
	[FailedAttemptCount] [integer] NULL,
	[RoleValue] [char](2) NULL,
	[UserStatusValue] [char](2) NULL,
	[PasswordRetrievalToken] [varchar](256) NULL,
	[LastLoginTime] [datetime] NULL,
	[PasswordRetrievalTokenExpirationDate] [datetime] NULL,
	[LastPasswordChangedDate] [datetime] NULL,
	[Session] [varchar](256) NULL,
	[Description] [nvarchar](2048) NULL,
	[ImageData] [varchar](max) NULL,
	[RevisionNumber] [int] NULL,
	[CreatedBy] [uniqueidentifier] NULL,
	[CreatedOn] [datetime] NULL,
	[ModifiedBy] [uniqueidentifier] NULL,
	[ModifiedOn] [datetime] NULL,
CONSTRAINT [PK_Member_Log] PRIMARY KEY CLUSTERED 
	([Sr] ASC)
	WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
	) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO


IF OBJECT_ID('dbo.[MemberView]', 'V') IS NOT NULL
DROP VIEW [dbo].[MemberView]
GO

/****** Object:  View [dbo].[MemberView]    Script Date: 4/29/2014 2:17:45 PM ******/
CREATE VIEW [dbo].[MemberView]
AS
	SELECT
	M.[Id]
	,M.[Name]
	,M.[TagId]
	,M.[Code]
	,M.[DateOfBirth]
	,M.[Address]
	,M.[Email]
	,M.[Mobile]
	,M.[ScreenMessage]
	,M.[ValidFrom]
	,M.[ValidTo]
	,M.[SendSms]
	,M.[SendEmail]
	,M.[UsePrint]
	,M.[Block]
	,M.[PasswordHash]
	,M.[PasswordSalt]
	,M.[FailedAttemptCount]
	,M.[RoleValue]
	,M.[UserStatusValue]
	,M.[PasswordRetrievalToken]
	,M.[LastLoginTime]
	,M.[PasswordRetrievalTokenExpirationDate]
	,M.[LastPasswordChangedDate]
	,M.[Session]
	,M.[Description]
	,M.[ImageData]
	,M.[RevisionNumber]
	,M.[CreatedBy]
	,CM.[Name] AS CreatedByName
	,M.[CreatedOn]
	,M.[ModifiedBy]
	,MM.[Name] AS ModifiedByName
	,M.[ModifiedOn]
	From Member M
	LEFT JOIN Member CM ON M.[CreatedBy] = CM.[Id]
	LEFT JOIN Member MM ON M.[ModifiedBy] = MM.[Id]

GO
SET ANSI_PADDING OFF 
GO


IF OBJECT_ID('dbo.[usp_MemberInsert]', N'P') IS NOT NULL
DROP PROCEDURE[dbo].[usp_MemberInsert]
GO

/****** Object:  StoredProcedure [dbo].[usp_MemberInsert]    Script Date: 4/29/2014 2:17:45 PM ******/
CREATE PROC [dbo].[usp_MemberInsert]
	@Id  [uniqueidentifier]
	,@Name [nvarchar](256)
	,@TagId [varchar](64)  = NULL
	,@Code [varchar](64)  = NULL
	,@DateOfBirth [datetime]  = NULL
	,@Address [nvarchar](256)  = NULL
	,@Email [varchar](128) 
	,@Mobile [varchar](32)  = NULL
	,@ScreenMessage [varchar](512)  = NULL
	,@ValidFrom [datetime]  = NULL
	,@ValidTo [datetime]  = NULL
	,@SendSms [bit]  = NULL
	,@SendEmail [bit]  = NULL
	,@UsePrint [bit]  = NULL
	,@Block [bit]  = NULL
	,@PasswordHash [varchar](256)  = NULL
	,@PasswordSalt [varchar](256)  = NULL
	,@FailedAttemptCount [integer]  = NULL
	,@RoleValue [char](2)  = NULL
	,@UserStatusValue [char](2)  = NULL
	,@PasswordRetrievalToken [varchar](256)  = NULL
	,@LastLoginTime [datetime]  = NULL
	,@PasswordRetrievalTokenExpirationDate [datetime]  = NULL
	,@LastPasswordChangedDate [datetime]  = NULL
	,@Session [varchar](256)  = NULL
	,@Description [nvarchar](2048)
	,@ImageData [varchar](max)
	,@UserID  [uniqueidentifier]

AS
	SET NOCOUNT ON 
	SET XACT_ABORT ON

	BEGIN TRAN

	INSERT INTO [dbo].[Member] ([Id], [Name], [TagId], [Code], [DateOfBirth], [Address], [Email], [Mobile], [ScreenMessage], [ValidFrom], [ValidTo], [SendSms], [SendEmail], [UsePrint], [Block], [PasswordHash], [PasswordSalt], [FailedAttemptCount], [RoleValue], [UserStatusValue], [PasswordRetrievalToken], [LastLoginTime], [PasswordRetrievalTokenExpirationDate], [LastPasswordChangedDate], [Session], [Description], [ImageData], [RevisionNumber], [CreatedBy], [CreatedOn], [ModifiedBy], [ModifiedOn])
	SELECT @Id, @Name, @TagId, @Code, @DateOfBirth, @Address, @Email, @Mobile, @ScreenMessage, @ValidFrom, @ValidTo, @SendSms, @SendEmail, @UsePrint, @Block, @PasswordHash, @PasswordSalt, @FailedAttemptCount, @RoleValue, @UserStatusValue, @PasswordRetrievalToken, @LastLoginTime, @PasswordRetrievalTokenExpirationDate, @LastPasswordChangedDate, @Session, @Description, @ImageData, 0, @UserId, GETDATE(), NULL, NULL

	INSERT INTO [dbo].[Member_log] ([Id], [Name], [TagId], [Code], [DateOfBirth], [Address], [Email], [Mobile], [ScreenMessage], [ValidFrom], [ValidTo], [SendSms], [SendEmail], [UsePrint], [Block], [PasswordHash], [PasswordSalt], [FailedAttemptCount], [RoleValue], [UserStatusValue], [PasswordRetrievalToken], [LastLoginTime], [PasswordRetrievalTokenExpirationDate], [LastPasswordChangedDate], [Session], [Description], [ImageData], [RevisionNumber], [CreatedBy], [CreatedOn], [ModifiedBy], [ModifiedOn])
	SELECT
	M.[Id]
	,M.[Name]
	,M.[TagId]
	,M.[Code]
	,M.[DateOfBirth]
	,M.[Address]
	,M.[Email]
	,M.[Mobile]
	,M.[ScreenMessage]
	,M.[ValidFrom]
	,M.[ValidTo]
	,M.[SendSms]
	,M.[SendEmail]
	,M.[UsePrint]
	,M.[Block]
	,M.[PasswordHash]
	,M.[PasswordSalt]
	,M.[FailedAttemptCount]
	,M.[RoleValue]
	,M.[UserStatusValue]
	,M.[PasswordRetrievalToken]
	,M.[LastLoginTime]
	,M.[PasswordRetrievalTokenExpirationDate]
	,M.[LastPasswordChangedDate]
	,M.[Session]
	,M.[Description]
	,M.[ImageData]
	,M.[RevisionNumber]
	,M.[CreatedBy]
	,M.[CreatedOn]
	,M.[ModifiedBy]
	,M.[ModifiedOn]
	From Member M

	WHERE M.[Id] = @Id


	COMMIT;

	SELECT
	M.[Id]
	,M.[Name]
	,M.[TagId]
	,M.[Code]
	,M.[DateOfBirth]
	,M.[Address]
	,M.[Email]
	,M.[Mobile]
	,M.[ScreenMessage]
	,M.[ValidFrom]
	,M.[ValidTo]
	,M.[SendSms]
	,M.[SendEmail]
	,M.[UsePrint]
	,M.[Block]
	,M.[PasswordHash]
	,M.[PasswordSalt]
	,M.[FailedAttemptCount]
	,M.[RoleValue]
	,M.[UserStatusValue]
	,M.[PasswordRetrievalToken]
	,M.[LastLoginTime]
	,M.[PasswordRetrievalTokenExpirationDate]
	,M.[LastPasswordChangedDate]
	,M.[Session]
	,M.[Description]
	,M.[ImageData]
	,M.[RevisionNumber]
	,M.[CreatedBy]
	,M.[CreatedOn]
	,M.[ModifiedBy]
	,M.[ModifiedOn]
	From Member M
	WHERE M.[Id] = @Id

GO


IF OBJECT_ID('dbo.[usp_MemberUpdate]', N'P') IS NOT NULL
DROP PROCEDURE[dbo].[usp_MemberUpdate]
GO

/****** Object:  StoredProcedure [dbo].[usp_MemberUpdate]    Script Date: 4/29/2014 2:17:45 PM ******/
CREATE PROC [dbo].[usp_MemberUpdate]
	@Id  [uniqueidentifier]
	,@Name [nvarchar](256)
	,@TagId [varchar](64)  = NULL
	,@Code [varchar](64)  = NULL
	,@DateOfBirth [datetime]  = NULL
	,@Address [nvarchar](256)  = NULL
	,@Email [varchar](128) 
	,@Mobile [varchar](32)  = NULL
	,@ScreenMessage [varchar](512)  = NULL
	,@ValidFrom [datetime]  = NULL
	,@ValidTo [datetime]  = NULL
	,@SendSms [bit]  = NULL
	,@SendEmail [bit]  = NULL
	,@UsePrint [bit]  = NULL
	,@Block [bit]  = NULL
	,@PasswordHash [varchar](256)  = NULL
	,@PasswordSalt [varchar](256)  = NULL
	,@FailedAttemptCount [integer]  = NULL
	,@RoleValue [char](2)  = NULL
	,@UserStatusValue [char](2)  = NULL
	,@PasswordRetrievalToken [varchar](256)  = NULL
	,@LastLoginTime [datetime]  = NULL
	,@PasswordRetrievalTokenExpirationDate [datetime]  = NULL
	,@LastPasswordChangedDate [datetime]  = NULL
	,@Session [varchar](256)  = NULL
	,@Description [nvarchar](2048)
	,@ImageData [varchar](max)
	,@UserID  [uniqueidentifier]

AS
	SET NOCOUNT ON 
	SET XACT_ABORT ON

	BEGIN TRAN

	UPDATE [dbo].[Member]
	SET [Id] = @Id, [Name] = @Name, [TagId] = @TagId, [Code] = @Code, [DateOfBirth] = @DateOfBirth, [Address] = @Address, [Email] = @Email, [Mobile] = @Mobile, [ScreenMessage] = @ScreenMessage, [ValidFrom] = @ValidFrom, [ValidTo] = @ValidTo, [SendSms] = @SendSms, [SendEmail] = @SendEmail, [UsePrint] = @UsePrint, [Block] = @Block, [PasswordHash] = @PasswordHash, [PasswordSalt] = @PasswordSalt, [FailedAttemptCount] = @FailedAttemptCount, [RoleValue] = @RoleValue, [UserStatusValue] = @UserStatusValue, [PasswordRetrievalToken] = @PasswordRetrievalToken, [LastLoginTime] = @LastLoginTime, [PasswordRetrievalTokenExpirationDate] = @PasswordRetrievalTokenExpirationDate, [LastPasswordChangedDate] = @LastPasswordChangedDate, [Session] = @Session, [Description] = @Description, [ImageData] = @ImageData, [RevisionNumber] = [RevisionNumber] + 1, [ModifiedBy] = @UserId, [ModifiedOn] = GETDATE()	WHERE [Id] = @Id


	INSERT INTO [dbo].[Member_log] ([Id], [Name], [TagId], [Code], [DateOfBirth], [Address], [Email], [Mobile], [ScreenMessage], [ValidFrom], [ValidTo], [SendSms], [SendEmail], [UsePrint], [Block], [PasswordHash], [PasswordSalt], [FailedAttemptCount], [RoleValue], [UserStatusValue], [PasswordRetrievalToken], [LastLoginTime], [PasswordRetrievalTokenExpirationDate], [LastPasswordChangedDate], [Session], [Description], [ImageData], [RevisionNumber], [CreatedBy], [CreatedOn], [ModifiedBy], [ModifiedOn])
	SELECT
	M.[Id]
	,M.[Name]
	,M.[TagId]
	,M.[Code]
	,M.[DateOfBirth]
	,M.[Address]
	,M.[Email]
	,M.[Mobile]
	,M.[ScreenMessage]
	,M.[ValidFrom]
	,M.[ValidTo]
	,M.[SendSms]
	,M.[SendEmail]
	,M.[UsePrint]
	,M.[Block]
	,M.[PasswordHash]
	,M.[PasswordSalt]
	,M.[FailedAttemptCount]
	,M.[RoleValue]
	,M.[UserStatusValue]
	,M.[PasswordRetrievalToken]
	,M.[LastLoginTime]
	,M.[PasswordRetrievalTokenExpirationDate]
	,M.[LastPasswordChangedDate]
	,M.[Session]
	,M.[Description]
	,M.[ImageData]
	,M.[RevisionNumber]
	,M.[CreatedBy]
	,M.[CreatedOn]
	,M.[ModifiedBy]
	,M.[ModifiedOn]
	From Member M

	WHERE M.[Id] = @Id


	COMMIT;

	SELECT
	M.[Id]
	,M.[Name]
	,M.[TagId]
	,M.[Code]
	,M.[DateOfBirth]
	,M.[Address]
	,M.[Email]
	,M.[Mobile]
	,M.[ScreenMessage]
	,M.[ValidFrom]
	,M.[ValidTo]
	,M.[SendSms]
	,M.[SendEmail]
	,M.[UsePrint]
	,M.[Block]
	,M.[PasswordHash]
	,M.[PasswordSalt]
	,M.[FailedAttemptCount]
	,M.[RoleValue]
	,M.[UserStatusValue]
	,M.[PasswordRetrievalToken]
	,M.[LastLoginTime]
	,M.[PasswordRetrievalTokenExpirationDate]
	,M.[LastPasswordChangedDate]
	,M.[Session]
	,M.[Description]
	,M.[ImageData]
	,M.[RevisionNumber]
	,M.[CreatedBy]
	,M.[CreatedOn]
	,M.[ModifiedBy]
	,M.[ModifiedOn]
	From Member M
	WHERE M.[Id] = @Id

GO


IF OBJECT_ID('dbo.[usp_MemberDelete]', N'P') IS NOT NULL
DROP PROCEDURE[dbo].[usp_MemberDelete]
GO

/****** Object:  StoredProcedure [dbo].[usp_MemberDelete]    Script Date: 4/29/2014 2:17:45 PM ******/
CREATE PROC [dbo].[usp_MemberDelete]
	@Id uniqueidentifier
AS
SET NOCOUNT ON
SET XACT_ABORT ON

BEGIN TRAN
	DELETE FROM [dbo].[Member] WHERE  [Id] = @Id
COMMIT;
GO